package com.langsin.dao.impl;

import com.langsin.dao.ProductDao;
import com.langsin.util.DataSourceUtil;
import com.langsin.vo.Product;
import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao{

    private Connection conn;
    private PreparedStatement ps;
    private ResultSet set;

    @Override
    public List<Product> queryAll() throws Exception {
        conn = DataSourceUtil.getConnection();
        String sql = "select * from product";
        this.ps = conn.prepareStatement(sql);
        this.set = ps.executeQuery();
        List<Product> list = new ArrayList<>();
        while(set.next()){
            Product product = new Product();
            product.setId(set.getString("id"));
            product.setPname(set.getString("p_name"));
            product.setPrice(set.getString("p_price"));
            product.setPtype(set.getString("p_type"));
            product.setPfac(set.getString("p_fac"));
            product.setDesc(set.getString("p_desc"));
            product.setPcode(set.getString("p_code"));
            list.add(product);
        }
        this.conn.close();
        return list;
    }

    @Override
    public List<Product> queryListByName(String pname, String pcode) throws Exception {
        List<Product> list = new ArrayList<>();
        conn = DataSourceUtil.getConnection();
        String sql = "select * from product WHERE 1 = 1 ";
        System.out.println(sql);
        if(!"".equals(pname)){
            sql = sql + " and p_name like ?";
        }
        if(!"".equals(pcode)){
            sql = sql + " and p_code = ?";
        }
        this.ps = this.conn.prepareStatement(sql);
        int index = 1;
        if(!"".equals(pname)){
            this.ps.setString(index,pname);
            index++;
        }
        if(!"".equals(pcode)){
            this.ps.setString(index,pcode);
        }
        this.set = this.ps.executeQuery();
        while(set.next()){
            Product product = new Product();
            product.setId(set.getString("id"));
            product.setPname(set.getString("p_name"));
            product.setPrice(set.getString("p_price"));
            product.setPtype(set.getString("p_type"));
            product.setPfac(set.getString("p_fac"));
            product.setDesc(set.getString("p_desc"));
            product.setPcode(set.getString("p_code"));
            list.add(product);
        }
        this.conn.close();
        return list;
    }

    @Override
    public int insertProduct(Product product) throws Exception {
        this.conn = DataSourceUtil.getConnection();
        String sql = "insert into product values(?,?,?,?,?,?,?)";
        this.ps = this.conn.prepareStatement(sql);
        this.ps.setString(1,product.getId());
        this.ps.setString(2,product.getPname());
        this.ps.setString(3,product.getPrice());
        this.ps.setString(4,product.getPtype());
        this.ps.setString(5,product.getPcode());
        this.ps.setString(6,product.getDesc());
        this.ps.setString(7,product.getPfac());
        int count = this.ps.executeUpdate();
        this.conn.close();
        return count;
    }

    @Override
    public Product queryProductById(String prodId) throws Exception {
        conn = DataSourceUtil.getConnection();
        String sql = "select * from product where id = ?";
        this.ps = conn.prepareStatement(sql);
        this.ps.setString(1,prodId);
        this.set = ps.executeQuery();
        Product product = null;
        while(set.next()){
            product = new Product();
            product.setId(set.getString("id"));
            product.setPname(set.getString("p_name"));
            product.setPrice(set.getString("p_price"));
            product.setPtype(set.getString("p_type"));
            product.setPfac(set.getString("p_fac"));
            product.setDesc(set.getString("p_desc"));
            product.setPcode(set.getString("p_code"));
        }
        this.conn.close();
        return product;
    }

    @Override
    public int updateProduct(Product product) throws Exception {
        conn = DataSourceUtil.getConnection();
        String sql = "update product set p_name = ?,p_price=?,p_code=?,where id = ? ";
        this.ps = this.conn.prepareStatement(sql);
        this.ps.setString(1,product.getPname());
        this.ps.setString(2,product.getPrice());
        this.ps.setString(3,product.getPcode());
        this.ps.setString(4,product.getId());
        int res = ps.executeUpdate();
        this.conn.close();
        return res;
    }
}
